package com.vegetable.modules.controller.outbound;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.NumberUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.poi.excel.style.StyleUtil;
import com.alibaba.fastjson.JSON;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.vegetable.common.annotation.PreventDuplicateSubmit;
import com.vegetable.common.annotation.SysLog;
import com.vegetable.common.utils.Constant;
import com.vegetable.common.utils.ExcelUtils;
import com.vegetable.common.utils.PageUtils;
import com.vegetable.common.utils.R;
import com.vegetable.modules.controller.AbstractController;
import com.vegetable.modules.entity.inbound.OrderCost;
import com.vegetable.modules.entity.outbound.SalesOrder;
import com.vegetable.modules.entity.outbound.SalesOut;
import com.vegetable.modules.entity.outbound.SalesOutDetail;
import com.vegetable.modules.entity.outbound.excel.SalesOutDetailExport;
import com.vegetable.modules.entity.outbound.excel.SalesOutExport;
import com.vegetable.modules.entity.outbound.excel.SalesOutImport;
import com.vegetable.modules.entity.outbound.po.SalesOutQueryPO;
import com.vegetable.modules.entity.outbound.po.SalesOutStatisticsPO;
import com.vegetable.modules.entity.outbound.po.UpdateHedgingStatePO;
import com.vegetable.modules.entity.outbound.po.UpdateOrderTimePO;
import com.vegetable.modules.entity.outbound.vo.SalesOutPageVO;
import com.vegetable.modules.service.inbound.IOrderCostService;
import com.vegetable.modules.service.outbound.ISalesOrderService;
import com.vegetable.modules.service.outbound.ISalesOutDetailService;
import com.vegetable.modules.service.outbound.ISalesOutService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.BeanUtils;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.validation.Valid;
import java.io.IOException;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.*;

import static cn.hutool.core.date.DatePattern.CHINESE_DATE_PATTERN;
import static org.apache.poi.ss.usermodel.HorizontalAlignment.CENTER;

/**
 * <p>
 * 销售出库单 前端控制器
 * </p>
 *
 * @author wangruijie
 * @since 2024-04-01
 */
@Slf4j
@RestController
@RequestMapping("/outStorage/salesOut")
@Api(value = "销售出库", tags = "销售出库")
public class SalesOutController extends AbstractController {
    @Resource
    private ISalesOutService outService;
    @Resource
    private IOrderCostService orderCostService;
    @Resource
    private ISalesOutDetailService iSalesOutDetailService;
    @Resource
    private ISalesOrderService salesOrderService;
    /**
     * 所有销售出库列表（包含分页数据和统计信息）
     */
    @PostMapping("/list")
    @ApiOperation(value = "条件查询销售出库列表", notes = "条件查询销售出库列表，返回分页数据和统计信息")
    @SysLog("条件查询销售出库列表")
    public R list(@RequestBody SalesOutQueryPO po){
        log.info("条件查询销售出库列表:{}", JSON.toJSONString(po));
        
        // 1. 查询分页数据
        IPage<SalesOut> pageData = outService.queryPage(po);
        
        // 2. 只有当 state=1（已出库）时才查询统计数据
        SalesOutPageVO result;
        if (po.getState() != null && po.getState() == 1) {
            // 查询统计数据并包装
            result = outService.queryStatistics(po);
            result.setPageData(pageData);
            log.info("已出库状态，返回分页和统计数据");
        } else {
            // 不查询统计数据，只返回分页数据
            result = new SalesOutPageVO();
            result.setPageData(pageData);
            log.info("非已出库状态，只返回分页数据，不进行统计");
        }
        
        return R.data(result);
    }

    @GetMapping("/exportOrders")
    @ApiOperation(value = "导出销售出库模板", notes = "导出销售出库模板")
    @SysLog("导出销售出库模板")
    public R exportOrders(@RequestBody SalesOutQueryPO po) throws IOException {
        IPage<SalesOut> page = outService.queryPage(po);
        List<SalesOut> outs = page.getRecords();
        List<SalesOutExport> orderList = new ArrayList<>();
        // 准备导出数据
        List<Map<String, Object>> dataList = new ArrayList<>();
        for(SalesOut out : outs){

        }
        // 生成导出excel对象
        ExportParams exportParams = new ExportParams();
        exportParams.setSheetName("销售出库列表");
        //开始导出
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, SalesOutExport.class, orderList);
        workbook.createCellStyle();
        StyleUtil.createHeadCellStyle(workbook);

        String fileName = "销售出库列表" + LocalDate.now().format(DateTimeFormatter.ofPattern("yyyyMMdd"));
        ExcelUtils.downLoadExcel(fileName,workbook);
        return R.success("");
    }

    /**
     * 详情销售出库
     */
    @SysLog(value = "详情销售出库")
    @GetMapping("/detailSalesOut")
    @ApiOperation(value = "根据id查询详情销售出库", notes = "根据id查询详情销售出库")
    public R detailSalesOut(Long id){
        if (id == null || id == -1){
            return R.fail("订单主键不能为空");
        }
        log.info("根据id查询详情销售出库：{}", JSON.toJSONString(id));
        return outService.detailSalesOut(id);
    }

    @SysLog(value = "根据id查询详情销售商品列表")
    @GetMapping("/detailsSalesOut")
    @ApiOperation(value = "根据id查询详情销售商品列表", notes = "根据id查询详情销售商品列表")
    public R detailsSalesOut(Long id){
        log.info("根据id查询详情销售商品列表：{}", JSON.toJSONString(id));
        return outService.detailsSalesOut(id);
    }

    @SysLog(value = "一键出库")
    @GetMapping("/oneClickOut")
    @ApiOperation(value = "一键出库", notes = "一键出库")
    public R oneClickOut(Long id) throws Exception {
        String userId = getUser().getUsername();
        List<String> operatorIdList = Arrays.asList("f006", "f007", "f032");
        if(!operatorIdList.contains(userId)){
            throw new Exception("当前操作人"+userId+"无权操作一键出库.");
        }
        if (id == null || id == -1){
            return R.fail("订单主键不能为空");
        }
        log.info("一键出库：{}", JSON.toJSONString(id));
        return outService.oneClickOut(id);
    }

    /**
     * 新增销售出库
     */
    @SysLog(value = "新增销售出库", recordEntityChange = true, entityClass = SalesOut.class)
    @PostMapping("/saveSalesOut")
    @ApiOperation(value = "新增销售出库", notes = "新增销售出库")
    public R saveSalesOut(@RequestBody SalesOut otherDelivery){
        log.info("新增销售出库：{}", JSON.toJSONString(otherDelivery));
        otherDelivery.setCreateUserId(getUserId());
        try {
            return outService.saveSalesOut(otherDelivery);
        } catch (Exception e) {
            e.printStackTrace();
            log.error("错误信息:{}",e.getLocalizedMessage());
            return R.fail(e.getMessage());
        }
    }

    /**
     * 修改销售出库
     */
    @SysLog(value = "修改销售出库", recordEntityChange = true, entityClass = SalesOut.class)
    @PostMapping("/updateSalesOut")
    @ApiOperation(value = "修改销售出库", notes = "修改销售出库")
    public R updateSalesOut(@RequestBody SalesOut otherDelivery) throws Exception {
        log.info("修改销售出库：{}", JSON.toJSONString(otherDelivery));
        if (otherDelivery.getId() == null || otherDelivery.getId() == -1){
            return R.fail("订单主键不能为空");
        }
        otherDelivery.setCreateUserId(getUserId());
        return outService.saveSalesOut(otherDelivery);
    }

    /**
     * 提交销售出库
     */
    @SysLog(value = "提交销售出库", recordEntityChange = true, entityClass = SalesOut.class)
    @PostMapping("/submitSalesOut")
    @ApiOperation(value = "提交销售出库", notes = "修改销售出库")
    @PreventDuplicateSubmit(expireTime = 5)
    public R submitSalesOut(@RequestBody SalesOut otherDelivery){
        log.info("提交销售出库：{}", JSON.toJSONString(otherDelivery));
        otherDelivery.setCreateUserId(getUserId());
        try {
            return outService.submitSalesOut(otherDelivery);
        } catch (Exception e) {
            e.printStackTrace();
            log.error("错误信息:{}",e.getLocalizedMessage());
            return R.fail(e.getMessage());
        }
    }

    /**
     * 删除销售出库
     */
    @SysLog(value = "删除销售出库", recordEntityChange = true, entityClass = SalesOut.class)
    @GetMapping("/deleteSalesOut")
    @ApiOperation(value = "根据id删除销售出库", notes = "根据id删除销售出库")
    public R deleteSalesOut(Long id){
        log.info("删除销售出库：{}", JSON.toJSONString(id));
        try {
            return outService.deleteSalesOut(id);
        } catch (Exception e) {
            e.printStackTrace();
            log.error("错误信息:{}",e.getLocalizedMessage());
            return R.fail(e.getMessage());
        }
    }

    /**
     * 更新销售出库单和销售订单的订单时间
     */
    @SysLog(value = "更新订单时间", recordEntityChange = true, entityClass = SalesOut.class)
    @PostMapping("/updateOrderTime")
    @ApiOperation(value = "更新订单时间", notes = "更新销售出库单和关联的销售订单的订单时间")
    public R updateOrderTime(@RequestBody @Valid UpdateOrderTimePO po) {
        log.info("更新订单时间：{}", JSON.toJSONString(po));
        try {
            return outService.updateOrderTime(po);
        } catch (Exception e) {
            e.printStackTrace();
            log.error("更新订单时间错误信息:{}", e.getLocalizedMessage());
            return R.fail(e.getMessage());
        }
    }

    /**
     * 查询（未对账金额 > 0）可收款的销售订单
     * @param customerId null
     * @return R
     * @author wrj
     * @since 2024/4/15 上午9:31
     */
    @GetMapping("/canCollectionList")
    @ApiOperation(value = "查询可收款的销售订单", notes = "查询可收款的销售订单")
    public R canCollectionList(Long customerId){
        log.info("条件查询销售出库列表{}", customerId);
        List<SalesOut> list= outService.canCollectionList(customerId);
        return R.data(list);
    }

    @GetMapping("/exportTemplate")
    @ApiOperation(value = "导出销售出库模板", notes = "导出销售出库模板")
    public R exportTemplate() throws IOException {
        List<SalesOutImport> orderList = new ArrayList<>();
        // 生成导出excel对象
        ExportParams exportParams = new ExportParams();
        exportParams.setSheetName("销售出库模板");
        //开始导出
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, SalesOutImport.class, orderList);
        workbook.createCellStyle();
        StyleUtil.createHeadCellStyle(workbook);

        String fileName = "销售出库模板" + LocalDate.now().format(DateTimeFormatter.ofPattern("yyyyMMdd"));
        ExcelUtils.downLoadExcel(fileName,workbook);
        return R.success("");
    }

    @GetMapping("/exportSalesOut")
    @ApiOperation(value = "导出销售出库详情", notes = "导出销售出库详情")
    public void exportSalesOut(Long id) throws IOException {
        R<SalesOut> salesR = detailSalesOut(id);
        SalesOut salesOut = salesR.getData();
//        SalesOut salesOut = outService.getById(id);
        List<SalesOutDetail> orderList = salesOut.getCommodities();
        List<OrderCost> orderCosts = orderCostService.selectByMainId(salesOut.getId());
        OrderCost zhuangche = orderCosts.stream().filter(el -> el.getCostClauseName().contains("装车")).findFirst().orElse(null);
        OrderCost dianban = orderCosts.stream().filter(el -> el.getCostClauseName().contains("垫板")).findFirst().orElse(null);
        OrderCost xieche = orderCosts.stream().filter(el -> el.getCostClauseName().contains("卸车")).findFirst().orElse(null);
        OrderCost nongcan = orderCosts.stream().filter(el -> el.getCostClauseName().contains("农残")).findFirst().orElse(null);
        OrderCost baoxianmo = orderCosts.stream().filter(el -> el.getCostClauseName().contains("保鲜膜")).findFirst().orElse(null);
        OrderCost yunfei = orderCosts.stream().filter(el -> el.getCostClauseName().contains("运费")).findFirst().orElse(null);
        OrderCost xiaoche = orderCosts.stream().filter(el -> el.getCostClauseName().contains("小车")).findFirst().orElse(null);
        OrderCost xiaogong = orderCosts.stream().filter(el -> el.getCostClauseName().contains("小工")).findFirst().orElse(null);
        OrderCost daishou = orderCosts.stream().filter(el -> el.getCostClauseName().contains("代收")).findFirst().orElse(null);
        OrderCost shichang = orderCosts.stream().filter(el -> el.getCostClauseName().contains("市场费")).findFirst().orElse(null);
        if(CollectionUtil.isEmpty(orderList)){
            throw new RuntimeException("销售出库bu存在");
        }
        // 生成导出excel对象
        ExportParams exportParams = new ExportParams();
        exportParams.setSheetName("销售出库详情");
        //开始导出
//        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, SalesOutExport.class, orderList);
        XSSFWorkbook  workbook = new XSSFWorkbook();
        CellStyle cellStyle = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setBold(true);
        font.setFontHeightInPoints((short) 14);
        cellStyle.setFont(font);
        cellStyle.setAlignment(CENTER);
        cellStyle.setBorderTop(BorderStyle.THIN);
        // 设置底部边框样式为粗实线，这里体现宽度变化，比细实线更粗
        cellStyle.setBorderBottom(BorderStyle.THIN);
        // 设置左边框样式为双实线，宽度也由对应的样式决定，双实线相对较粗
        cellStyle.setBorderLeft(BorderStyle.THIN);
        // 设置右边框样式为点划线，有其特定宽度表现
        cellStyle.setBorderRight(BorderStyle.THIN);

        // 创建数字格式样式
        CellStyle numberStyle = workbook.createCellStyle();
        DataFormat format = workbook.createDataFormat();
        numberStyle.setDataFormat(format.getFormat("0.00")); // 保留2位小数
        numberStyle.setFont(font);
        numberStyle.setAlignment(CENTER);
        numberStyle.setBorderTop(BorderStyle.THIN);
        // 设置底部边框样式为粗实线，这里体现宽度变化，比细实线更粗
        numberStyle.setBorderBottom(BorderStyle.THIN);
        // 设置左边框样式为双实线，宽度也由对应的样式决定，双实线相对较粗
        numberStyle.setBorderLeft(BorderStyle.THIN);
        // 设置右边框样式为点划线，有其特定宽度表现
        numberStyle.setBorderRight(BorderStyle.THIN);


        XSSFSheet sheet = workbook.createSheet("Sheet1");
        for(int i = 0; i < 50; i++){
            sheet.setColumnWidth(i, 15 * 256);
        }
        if(salesOut.getCustomerName().contains("乌牛") || salesOut.getCustomerName().contains("温州菜")){
            extractedMModel (salesOut, orderList, zhuangche, dianban, xieche, nongcan, baoxianmo, yunfei, xiaoche, xiaogong, daishou, shichang, sheet);
        }else{
            extractedSMModel (salesOut, orderList, zhuangche, dianban, xieche, nongcan, baoxianmo, yunfei, xiaoche, xiaogong, daishou, shichang, sheet);
        }
        int maxCellNum = sheet.getRow(2).getLastCellNum();
        int lastRowNum = sheet.getRow(sheet.getLastRowNum()).getLastCellNum();
        for (int i = 0; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            int currentNum = i < orderList.size() + 4 ? maxCellNum : lastRowNum;
            if (row!= null) {
                for (int j = 0; j < currentNum; j++) {
                    Cell ccell = row.getCell(j) == null ? row.createCell(j) : row.getCell(j);
                    if(ccell.getCellType().equals(CellType.FORMULA) || ccell.getCellType().equals(CellType.NUMERIC)){
                        ccell.setCellStyle(numberStyle);
                    }else{
                        ccell.setCellStyle(cellStyle);
                    }
                }
            }
        }

        StyleUtil.createHeadCellStyle(workbook);

        String fileName = "11111111111" + LocalDate.now().format(DateTimeFormatter.ofPattern("yyyyMMdd"));
        ExcelUtils.downLoadExcel(fileName,workbook);
//        return R.success("");
    }

    private void extractedSMModel (SalesOut salesOut, List<SalesOutDetail> orderList, OrderCost zhuangche, OrderCost dianban, OrderCost xieche, OrderCost nongcan, OrderCost baoxianmo, OrderCost yunfei, OrderCost xiaoche, OrderCost xiaogong, OrderCost daishou, OrderCost shichang, XSSFSheet sheet) {
        List<String> headRows = Arrays.asList("品名", "件数", "总重", "净重（件）", "包装费（KG)", "包装", "包装费总额", "裸价", "杂费单价", "到地成本价", "蔬菜金额", "应付金额");
        CellRangeAddress region = new CellRangeAddress(0, 0, 0, 11);
        sheet.addMergedRegion(region);
        XSSFRow titleRow = sheet.createRow(0 );
        XSSFCell titleCell = titleRow.createCell(0);
        titleCell.setCellValue(salesOut.getCustomerName());

        CellRangeAddress dateRegion = new CellRangeAddress(1, 1, 0, 3);
        sheet.addMergedRegion(dateRegion);
        XSSFRow dateRow = sheet.createRow(1 );
        XSSFCell dateCell = dateRow.createCell(0);
        dateCell.setCellValue(DateUtil.format(salesOut.getOrderTime(), CHINESE_DATE_PATTERN) + "     蔬菜    单位：KG");
        XSSFCell carCell = dateRow.createCell(4);
        carCell.setCellValue("车");
        XSSFCell carIDCell = dateRow.createCell(5);
        carIDCell.setCellValue("车牌:");
        XSSFCell emptyCell = dateRow.createCell(6);
        emptyCell.setCellValue("");
        XSSFCell telCell = dateRow.createCell(8);
        telCell.setCellValue("电话");
        CellRangeAddress telRegion = new CellRangeAddress(1, 1, 9, 11);
        sheet.addMergedRegion(telRegion);
        XSSFCell telRegionCell = dateRow.createCell(9);
        telRegionCell.setCellValue(salesOut.getConnectMobile());


        XSSFRow headRow = sheet.createRow(2);
        for(int i = 0; i< headRows.size(); i++){
            XSSFCell cell = headRow.createCell(i);
            cell.setCellValue(headRows.get(i));
        }
        for (int index = 0; index < orderList.size(); index++) {
            int rowNum = index + 3;
            XSSFRow row = sheet.createRow(rowNum);
            SalesOutDetail detail = orderList.get(index);
            XSSFCell commodityNameCell = row.createCell(0);
            commodityNameCell.setCellValue(detail.getCommodityName());
            XSSFCell numberCell = row.createCell(1);
            numberCell.setCellValue(detail.getNumber());
            XSSFCell standardCell = row.createCell(3);
            if(NumberUtil.equals(BigDecimal.ZERO, detail.getStandard())){
                standardCell.setCellFormula("C" + (rowNum + 1) + "/B" + (rowNum + 1));
            }else{
                standardCell.setCellValue(detail.getStandard().doubleValue());
            }
            XSSFCell weightCell = row.createCell(2);
            if(NumberUtil.equals(BigDecimal.ZERO, detail.getStandard())){
                weightCell.setCellValue(detail.getWeight().doubleValue());
            }else{
                weightCell.setCellFormula("D" + (rowNum + 1) + "*B" + (rowNum + 1));
            }
            XSSFCell packageCell = row.createCell(4);
            packageCell.setCellFormula("F" + (rowNum + 1) + "/D" + (rowNum + 1));
            XSSFCell packUnitCell = row.createCell(5);
            packUnitCell.setCellValue(detail.getPackUnitPrice().doubleValue());
            XSSFCell packTotalCell = row.createCell(6);
            packTotalCell.setCellFormula("F" + (rowNum + 1) + "*B" + (rowNum + 1));
            if(!Objects.isNull(detail.getUnitPrice())){
                XSSFCell goodsUnitCell = row.createCell(7);
                goodsUnitCell.setCellValue(detail.getUnitPrice().doubleValue());
            }
            XSSFCell extrasCell = row.createCell(8);
            String s = "(B" + (orderList.size() + 7) + "+B" + (orderList.size() + 8) + "+B" + (orderList.size() + 9) + "+B" + (orderList.size() + 10) + "+B" + (orderList.size() + 11)+ "+B" + (orderList.size() + 12)+ "+B" + (orderList.size() + 13)+ "+B" + (orderList.size() + 14) + "+B" + (orderList.size() + 15) + "+B" + (orderList.size() + 16) + ")/SUM(C" + 4 + ":C" + (orderList.size() + 3) + ")";
            extrasCell.setCellFormula(s);
            XSSFCell daodichengbenCell = row.createCell(9);
            daodichengbenCell.setCellFormula("E" + (rowNum + 1) + "+H" + (rowNum + 1) + "+I" + (rowNum + 1));
            XSSFCell goodsCell = row.createCell(10);
            goodsCell.setCellFormula("C" + (rowNum + 1) + "*H" + (rowNum + 1));
            XSSFCell shouldPayCell = row.createCell(11);
            shouldPayCell.setCellFormula("C" + (rowNum + 1) + "*J" + (rowNum + 1));
        }
        XSSFRow sumRow = sheet.createRow(orderList.size() + 3);
        XSSFCell cell = sumRow.createCell(0);
        cell.setCellValue("合计");
        XSSFCell totalNumbercell = sumRow.createCell(1);
        totalNumbercell.setCellFormula("SUM(B"+4+":B"+(orderList.size() + 3) +")");
        XSSFCell totalWeightcell = sumRow.createCell(2);
        totalWeightcell.setCellFormula("SUM(C"+4+":C"+(orderList.size() + 3)+")");
        XSSFCell totalPackcell = sumRow.createCell(6);
        totalPackcell.setCellFormula("SUM(G"+4+":G"+(orderList.size() + 3)+")");
        XSSFCell totalGoodscell = sumRow.createCell(10);
        totalGoodscell.setCellFormula("SUM(K"+4+":K"+(orderList.size() + 3)+")");
        XSSFCell totalShouldPaycell = sumRow.createCell(11);
        totalShouldPaycell.setCellFormula("SUM(L"+4+":L"+(orderList.size() + 3)+")");
        XSSFRow goodsMoneyRow = sheet.createRow(orderList.size() + 4);
        XSSFCell goodsMoneyNameCell = goodsMoneyRow.createCell(0);
        goodsMoneyNameCell.setCellValue("蔬菜金额");
        XSSFCell goodsMoneyValueCell = goodsMoneyRow.createCell(1);
        goodsMoneyValueCell.setCellFormula("SUM(K"+4+":K"+(orderList.size() + 3)+")");
        XSSFRow packMoneyRow = sheet.createRow(orderList.size() + 5);
        XSSFCell packMoneyNameCell = packMoneyRow.createCell(0);
        packMoneyNameCell.setCellValue("包装费");
        XSSFCell packMoneyValueCell = packMoneyRow.createCell(1);
        packMoneyValueCell.setCellFormula("SUM(G"+4+":G"+(orderList.size() + 3)+")");
        XSSFRow zhuangcheMoneyRow = sheet.createRow(orderList.size() + 6);
        XSSFCell zhuangcheMoneyNameCell = zhuangcheMoneyRow.createCell(0);
        zhuangcheMoneyNameCell.setCellValue("装车");
        XSSFCell zhuangcheMoneyValueCell = zhuangcheMoneyRow.createCell(1);
        zhuangcheMoneyValueCell.setCellValue(ObjectUtil.isNotNull(zhuangche)? zhuangche.getMoney().doubleValue() : 0);
        XSSFRow xiaocheMoneyRow = sheet.createRow(orderList.size() + 7);
        XSSFCell xiaochecheMoneyNameCell = xiaocheMoneyRow.createCell(0);
        xiaochecheMoneyNameCell.setCellValue("小车");
        XSSFCell xiaochecheMoneyValueCell = xiaocheMoneyRow.createCell(1);
        xiaochecheMoneyValueCell.setCellFormula("SUM(C"+4+":C"+(orderList.size() + 3)+")*0.03");
        XSSFRow xiaogongMoneyRow = sheet.createRow(orderList.size() + 8);
        XSSFCell xiaogongMoneyNameCell = xiaogongMoneyRow.createCell(0);
        xiaogongMoneyNameCell.setCellValue("小工");
        XSSFCell xiaogongMoneyValueCell = xiaogongMoneyRow.createCell(1);
//        xiaogongMoneyValueCell.setCellValue(ObjectUtil.isNotNull(xiaogong)? xiaogong.getMoney().doubleValue() : 0);
        xiaogongMoneyValueCell.setCellFormula("SUM(C"+4+":C"+(orderList.size() + 3)+")*0.03");
        XSSFRow daishouMoneyRow = sheet.createRow(orderList.size() + 9);
        XSSFCell daishouMoneyNameCell = daishouMoneyRow.createCell(0);
        daishouMoneyNameCell.setCellValue("代收");
        XSSFCell daishouMoneyValueCell = daishouMoneyRow.createCell(1);
        daishouMoneyValueCell.setCellFormula("SUM(C"+4+":C"+(orderList.size() + 3)+")*0.1");
        XSSFRow dianbanMoneyRow = sheet.createRow(orderList.size() + 10);
        XSSFCell dianbanMoneyNameCell = dianbanMoneyRow.createCell(0);
        dianbanMoneyNameCell.setCellValue("垫板");
        XSSFCell dianbanMoneyValueCell = dianbanMoneyRow.createCell(1);
        dianbanMoneyValueCell.setCellValue(ObjectUtil.isNotNull(dianban)? dianban.getMoney().doubleValue() : 0);
        XSSFRow xiecheMoneyRow = sheet.createRow(orderList.size() + 11);
        XSSFCell xiecheMoneyNameCell = xiecheMoneyRow.createCell(0);
        xiecheMoneyNameCell.setCellValue("卸车");
        XSSFCell xiecheMoneyValueCell = xiecheMoneyRow.createCell(1);
        xiecheMoneyValueCell.setCellValue(ObjectUtil.isNotNull(xieche)? xieche.getMoney().doubleValue() : 0);
        XSSFRow nongcanRow = sheet.createRow(orderList.size() + 12);
        XSSFCell nongcanMoneyNameCell = nongcanRow.createCell(0);
        nongcanMoneyNameCell.setCellValue("农残");
        XSSFCell nongcanMoneyValueCell = nongcanRow.createCell(1);
        nongcanMoneyValueCell.setCellValue(ObjectUtil.isNotNull(nongcan)? nongcan.getMoney().doubleValue() : 0);
        XSSFRow baoxianmoRow = sheet.createRow(orderList.size() + 13);
        XSSFCell baoxianmoMoneyNameCell = baoxianmoRow.createCell(0);
        baoxianmoMoneyNameCell.setCellValue("保鲜膜");
        XSSFCell baoxianmoMoneyValueCell = baoxianmoRow.createCell(1);
        baoxianmoMoneyValueCell.setCellValue(ObjectUtil.isNotNull(baoxianmo)? baoxianmo.getMoney().doubleValue() : 0);
        XSSFRow yunfeiMoneyRow = sheet.createRow(orderList.size() + 14);
        XSSFCell yunfeiMoneyNameCell = yunfeiMoneyRow.createCell(0);
        yunfeiMoneyNameCell.setCellValue("运费");
        XSSFCell yunfeiMoneyValueCell = yunfeiMoneyRow.createCell(1);
        yunfeiMoneyValueCell.setCellValue(ObjectUtil.isNotNull(yunfei)? yunfei.getMoney().doubleValue() : 0);

        XSSFRow shichangMoneyRow = sheet.createRow(orderList.size() + 15);
        XSSFCell shichangMoneyNameCell = shichangMoneyRow.createCell(0);
        shichangMoneyNameCell.setCellValue("市场");
        XSSFCell shichangMoneyValueCell = shichangMoneyRow.createCell(1);
        shichangMoneyValueCell.setCellValue(ObjectUtil.isNotNull(shichang)? shichang.getMoney().doubleValue() : 0);

        XSSFRow totalMoneyRow = sheet.createRow(orderList.size() + 16);
        XSSFCell totalMoneyNameCell = totalMoneyRow.createCell(0);
        totalMoneyNameCell.setCellValue("合计");
        XSSFCell totalMoneyValueCell = totalMoneyRow.createCell(1);
        totalMoneyValueCell.setCellFormula("SUM(B"+(orderList.size() + 5)+":B"+(orderList.size() + 16)+")");
        XSSFCell totalMoney2ValueCell = totalMoneyRow.createCell(2);
        totalMoney2ValueCell.setCellValue(0);
        XSSFCell totalMoney3ValueCell = totalMoneyRow.createCell(3);
        totalMoney3ValueCell.setCellFormula("B"+(orderList.size() + 16 + 1)+"+C"+(orderList.size() + 16 + 1));
    }
    private void extractedMModel (SalesOut salesOut, List<SalesOutDetail> orderList, OrderCost zhuangche, OrderCost dianban, OrderCost xieche, OrderCost nongcan, OrderCost baoxianmo, OrderCost yunfei, OrderCost xiaoche, OrderCost xiaogong, OrderCost daishou, OrderCost shichang, XSSFSheet sheet) {
        List<String> headRows = Arrays.asList("金额/件", "到地成本价", "件数", "裸价", "品名", "件数", "总重", "净重（件）", "包装费（KG)", "包装", "包装费总额", "裸价", "杂费单价", "到地成本价", "蔬菜金额", "应付金额");
        CellRangeAddress region = new CellRangeAddress(0, 0, 0, 15);
        sheet.addMergedRegion(region);
        XSSFRow titleRow = sheet.createRow(0 );
        XSSFCell titleCell = titleRow.createCell(0);
        titleCell.setCellValue(salesOut.getCustomerName());

        CellRangeAddress dateRegion = new CellRangeAddress(1, 1, 0, 3);
        sheet.addMergedRegion(dateRegion);
        XSSFRow dateRow = sheet.createRow(1 );
        XSSFCell dateCell = dateRow.createCell(0);
        dateCell.setCellValue(DateUtil.format(salesOut.getOrderTime(), CHINESE_DATE_PATTERN) + "     蔬菜    单位：KG");
        XSSFCell carCell = dateRow.createCell(4);
        carCell.setCellValue("车");
        XSSFCell carIDCell = dateRow.createCell(5);
        carIDCell.setCellValue("车牌:");
        XSSFCell emptyCell = dateRow.createCell(6);
        emptyCell.setCellValue("");
        XSSFCell telCell = dateRow.createCell(8);
        telCell.setCellValue("电话");
        CellRangeAddress telRegion = new CellRangeAddress(1, 1, 9, 11);
        sheet.addMergedRegion(telRegion);
        XSSFCell telRegionCell = dateRow.createCell(9);
        telRegionCell.setCellValue(salesOut.getConnectMobile());


        XSSFRow headRow = sheet.createRow(2);
        for(int i = 0; i< headRows.size(); i++){
            XSSFCell cell = headRow.createCell(i);
            cell.setCellValue(headRows.get(i));
        }
        for (int index = 0; index < orderList.size(); index++) {
            int rowNum = index + 3;
            XSSFRow row = sheet.createRow(rowNum);
            SalesOutDetail detail = orderList.get(index);
            row.createCell(0).setCellFormula("(G" + (rowNum + 1) + "*N" + (rowNum + 1)+")/"+detail.getNumber());
            row.createCell(1).setCellFormula("I" + (rowNum + 1) + "+L" + (rowNum + 1) + "+M" + (rowNum + 1));
            row.createCell(2).setCellValue(detail.getNumber());
            if(!Objects.isNull(detail.getUnitPrice())){
                row.createCell(3).setCellValue(detail.getUnitPrice().doubleValue());
            }
            XSSFCell commodityNameCell = row.createCell(4);
            commodityNameCell.setCellValue(detail.getCommodityName());
            XSSFCell numberCell = row.createCell(5);
            numberCell.setCellValue(detail.getNumber());
            XSSFCell standardCell = row.createCell(7);
            if(NumberUtil.equals(BigDecimal.ZERO, detail.getStandard())){
                standardCell.setCellFormula("G" + (rowNum + 1) + "/F" + (rowNum + 1));
            }else{
                standardCell.setCellValue(detail.getStandard().doubleValue());
            }
            XSSFCell weightCell = row.createCell(6);
            if(NumberUtil.equals(BigDecimal.ZERO, detail.getStandard())){
                weightCell.setCellValue(detail.getWeight().doubleValue());
            }else{
                weightCell.setCellFormula("H" + (rowNum + 1) + "*F" + (rowNum + 1));
            }
            XSSFCell packageCell = row.createCell(8);
            packageCell.setCellFormula("J" + (rowNum + 1) + "/H" + (rowNum + 1));
            XSSFCell packUnitCell = row.createCell(9);
            packUnitCell.setCellValue(detail.getPackUnitPrice().doubleValue());
            XSSFCell packTotalCell = row.createCell(10);
            packTotalCell.setCellFormula("J" + (rowNum + 1) + "*F" + (rowNum + 1));
            if(!Objects.isNull(detail.getUnitPrice())){
                XSSFCell goodsUnitCell = row.createCell(11);
                goodsUnitCell.setCellValue(detail.getUnitPrice().doubleValue());
            }
            XSSFCell extrasCell = row.createCell(12);
            String s = "(F" + (orderList.size() + 7) + "+F" + (orderList.size() + 8) + "+F" + (orderList.size() + 9) + "+F" + (orderList.size() + 10) + "+F"
                    + (orderList.size() + 11)+ "+F" + (orderList.size() + 12)+ "+F" + (orderList.size() + 13)+ "+F" + (orderList.size() + 14) + "+F" + (orderList.size() + 15)+ "+F" + (orderList.size() + 16)  +
                    ")/SUM(G" + 4 + ":G" + (orderList.size() + 3) + ")";
            extrasCell.setCellFormula(s);
            XSSFCell daodichengbenCell = row.createCell(13);
            daodichengbenCell.setCellFormula("I" + (rowNum + 1) + "+L" + (rowNum + 1) + "+M" + (rowNum + 1));
            XSSFCell goodsCell = row.createCell(14);
            goodsCell.setCellFormula("G" + (rowNum + 1) + "*L" + (rowNum + 1));
            XSSFCell shouldPayCell = row.createCell(15);
            shouldPayCell.setCellFormula("G" + (rowNum + 1) + "*N" + (rowNum + 1));
        }
        XSSFRow sumRow = sheet.createRow(orderList.size() + 3);
        XSSFCell cell = sumRow.createCell(4);
        cell.setCellValue("合计");
        XSSFCell totalNumbercell = sumRow.createCell(5);
        totalNumbercell.setCellFormula("SUM(F"+4+":F"+(orderList.size() + 3) +")");
        XSSFCell totalWeightcell = sumRow.createCell(6);
        totalWeightcell.setCellFormula("SUM(G"+4+":G"+(orderList.size() + 3)+")");
        XSSFCell totalPackcell = sumRow.createCell(10);
        totalPackcell.setCellFormula("SUM(K"+4+":K"+(orderList.size() + 3)+")");
        XSSFCell totalGoodscell = sumRow.createCell(14);
        totalGoodscell.setCellFormula("SUM(O"+4+":O"+(orderList.size() + 3)+")");
        XSSFCell totalShouldPaycell = sumRow.createCell(15);
        totalShouldPaycell.setCellFormula("SUM(P"+4+":P"+(orderList.size() + 3)+")");
        XSSFRow goodsMoneyRow = sheet.createRow(orderList.size() + 4);
        XSSFCell goodsMoneyNameCell = goodsMoneyRow.createCell(4);
        goodsMoneyNameCell.setCellValue("蔬菜金额");
        XSSFCell goodsMoneyValueCell = goodsMoneyRow.createCell(5);
        goodsMoneyValueCell.setCellFormula("SUM(O"+4+":O"+(orderList.size() + 3)+")");
        XSSFRow packMoneyRow = sheet.createRow(orderList.size() + 5);
        XSSFCell packMoneyNameCell = packMoneyRow.createCell(4);
        packMoneyNameCell.setCellValue("包装费");
        XSSFCell packMoneyValueCell = packMoneyRow.createCell(5);
        packMoneyValueCell.setCellFormula("SUM(K"+4+":K"+(orderList.size() + 3)+")");
        XSSFRow zhuangcheMoneyRow = sheet.createRow(orderList.size() + 6);
        XSSFCell zhuangcheMoneyNameCell = zhuangcheMoneyRow.createCell(4);
        zhuangcheMoneyNameCell.setCellValue("装车");
        XSSFCell zhuangcheMoneyValueCell = zhuangcheMoneyRow.createCell(5);
        zhuangcheMoneyValueCell.setCellValue(ObjectUtil.isNotNull(zhuangche)? zhuangche.getMoney().doubleValue() : 0);
        XSSFRow xiaocheMoneyRow = sheet.createRow(orderList.size() + 7);
        XSSFCell xiaochecheMoneyNameCell = xiaocheMoneyRow.createCell(4);
        xiaochecheMoneyNameCell.setCellValue("小车");
        XSSFCell xiaochecheMoneyValueCell = xiaocheMoneyRow.createCell(5);
        xiaochecheMoneyValueCell.setCellFormula("SUM(G"+4+":G"+(orderList.size() + 3)+")*0.03");
        XSSFRow xiaogongMoneyRow = sheet.createRow(orderList.size() + 8);
        XSSFCell xiaogongMoneyNameCell = xiaogongMoneyRow.createCell(4);
        xiaogongMoneyNameCell.setCellValue("小工");
        XSSFCell xiaogongMoneyValueCell = xiaogongMoneyRow.createCell(5);
//        xiaogongMoneyValueCell.setCellValue(ObjectUtil.isNotNull(xiaogong)? xiaogong.getMoney().doubleValue() : 0);
        xiaogongMoneyValueCell.setCellFormula("SUM(G"+4+":G"+(orderList.size() + 3)+")*0.03");
        XSSFRow daishouMoneyRow = sheet.createRow(orderList.size() + 9);
        XSSFCell daishouMoneyNameCell = daishouMoneyRow.createCell(4);
        daishouMoneyNameCell.setCellValue("代收");
        XSSFCell daishouMoneyValueCell = daishouMoneyRow.createCell(5);
        daishouMoneyValueCell.setCellFormula("SUM(G"+4+":G"+(orderList.size() + 3)+")*0.1");
        XSSFRow dianbanMoneyRow = sheet.createRow(orderList.size() + 10);
        XSSFCell dianbanMoneyNameCell = dianbanMoneyRow.createCell(4);
        dianbanMoneyNameCell.setCellValue("垫板");
        XSSFCell dianbanMoneyValueCell = dianbanMoneyRow.createCell(5);
        dianbanMoneyValueCell.setCellValue(ObjectUtil.isNotNull(dianban)? dianban.getMoney().doubleValue() : 0);
        XSSFRow xiecheMoneyRow = sheet.createRow(orderList.size() + 11);
        XSSFCell xiecheMoneyNameCell = xiecheMoneyRow.createCell(4);
        xiecheMoneyNameCell.setCellValue("卸车");
        XSSFCell xiecheMoneyValueCell = xiecheMoneyRow.createCell(5);
        xiecheMoneyValueCell.setCellValue(ObjectUtil.isNotNull(xieche)? xieche.getMoney().doubleValue() : 0);
        XSSFRow nongcanRow = sheet.createRow(orderList.size() + 12);
        XSSFCell nongcanMoneyNameCell = nongcanRow.createCell(4);
        nongcanMoneyNameCell.setCellValue("农残");
        XSSFCell nongcanMoneyValueCell = nongcanRow.createCell(5);
        nongcanMoneyValueCell.setCellValue(ObjectUtil.isNotNull(nongcan)? nongcan.getMoney().doubleValue() : 0);
        XSSFRow baoxianmoRow = sheet.createRow(orderList.size() + 13);
        XSSFCell baoxianmoMoneyNameCell = baoxianmoRow.createCell(4);
        baoxianmoMoneyNameCell.setCellValue("保鲜膜");
        XSSFCell baoxianmoMoneyValueCell = baoxianmoRow.createCell(5);
        baoxianmoMoneyValueCell.setCellValue(ObjectUtil.isNotNull(baoxianmo)? baoxianmo.getMoney().doubleValue() : 0);
        XSSFRow yunfeiMoneyRow = sheet.createRow(orderList.size() + 14);
        XSSFCell yunfeiMoneyNameCell = yunfeiMoneyRow.createCell(4);
        yunfeiMoneyNameCell.setCellValue("运费");
        XSSFCell yunfeiMoneyValueCell = yunfeiMoneyRow.createCell(5);
        yunfeiMoneyValueCell.setCellValue(ObjectUtil.isNotNull(yunfei)? yunfei.getMoney().doubleValue() : 0);

        XSSFRow shichangMoneyRow = sheet.createRow(orderList.size() + 15);
        XSSFCell shichangMoneyNameCell = shichangMoneyRow.createCell(4);
        shichangMoneyNameCell.setCellValue("市场");
        XSSFCell shichangMoneyValueCell = shichangMoneyRow.createCell(5);
        shichangMoneyValueCell.setCellValue(ObjectUtil.isNotNull(shichang)? shichang.getMoney().doubleValue() : 0);

        XSSFRow totalMoneyRow = sheet.createRow(orderList.size() + 16);
        XSSFCell totalMoneyNameCell = totalMoneyRow.createCell(4);
        totalMoneyNameCell.setCellValue("合计");
        XSSFCell totalMoneyValueCell = totalMoneyRow.createCell(5);
        totalMoneyValueCell.setCellFormula("SUM(F"+(orderList.size() + 5)+":F"+(orderList.size() + 16)+")");
        XSSFCell totalMoney2ValueCell = totalMoneyRow.createCell(6);
        totalMoney2ValueCell.setCellValue(0);
        XSSFCell totalMoney3ValueCell = totalMoneyRow.createCell(8);
        totalMoney3ValueCell.setCellFormula("F"+(orderList.size() + 16 + 1)+"+G"+(orderList.size() + 16 + 1));
    }

    /**
     * 批量导入销售出库
     */
    @ApiOperation("批量导入销售出库")
    @PostMapping("/uploadSalesOut")
    public R uploadSalesOut(
            @RequestParam(value = "file", required = true) MultipartFile file) throws Exception {
        ImportParams importParams = new ImportParams();
        List<SalesOutImport> orderList = ExcelImportUtil.importExcel(file.getInputStream(), SalesOutImport.class, importParams);
        // 失败原因列表
        List<String> failReasons = new ArrayList<>();
        // 验证导入数量
        //
        if(CollectionUtil.isEmpty(orderList)){
            String failReason = "导入的表格不能为空";
            failReasons.add(failReason);
            return R.data(failReasons);
        }
        try {
            return outService.uploadSalesOut(orderList);
        } catch (Exception e) {
            e.printStackTrace();
            log.error("错误信息:{}",e.getLocalizedMessage());
            return R.fail(e.getMessage());
        }
    }

    @SysLog(value = "提交销售出库")
    @PostMapping("/currentSalesOut")
    @ApiOperation(value = "校验销售出库", notes = "校验销售出库")
    public R currentSalesOut(@RequestBody SalesOut otherDelivery) throws Exception {
        log.info("校验销售出库：{}", JSON.toJSONString(otherDelivery));
        otherDelivery.setCreateUserId(getUserId());
        return outService.currentSalesOut(otherDelivery);
    }

    @SysLog(value = "销售明细统计情况")
    @PostMapping("/statisticsSalesOut")
    @ApiOperation(value = "销售明细统计情况", notes = "销售明细统计情况")
    public R salesOutStatisticsVO(@RequestBody SalesOutStatisticsPO po){
        po.setState(1);
        return outService.salesOutStatisticsVO(po);
    }

    @SysLog(value = "销售明细统计情况")
    @PostMapping("/exportStatisticsSalesOut")
    @ApiOperation(value = "销售明细统计情况", notes = "销售明细统计情况")
    public R exportStatisticsSalesOut(@RequestBody SalesOutStatisticsPO po) throws IOException {
        po.setState(1);
        List<SalesOutDetailExport> detailVOS = outService.exportStatisticsSalesOut(po);
        // 生成导出excel对象
        ExportParams exportParams = new ExportParams();
        exportParams.setAutoSize(true);
        exportParams.setSheetName("出库详情");
        //开始导出
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, SalesOutDetailExport.class, detailVOS);
        workbook.createCellStyle();
        StyleUtil.createHeadCellStyle(workbook);

        String fileName = "日交接单模板" + LocalDate.now().format(DateTimeFormatter.ofPattern("yyyyMMdd"));
        ExcelUtils.downLoadExcel(fileName,workbook);
        return R.success("");
    }

    @SysLog(value = "销售首款情况")
    @PostMapping("/sequenceToGeneral")
    @ApiOperation(value = "销售首款情况", notes = "销售首款情况")
    public R sequenceToGeneral(@RequestBody SalesOutStatisticsPO po){
        log.info("销售首款情况：{}", JSON.toJSONString(po));
        return outService.sequenceToGeneral(po);
    }

    @SysLog(value = "销售首款明细")
    @PostMapping("/sequenceToDetail")
    @ApiOperation(value = "销售首款情况", notes = "销售首款情况")
    public R sequenceToDetail(@RequestBody SalesOutStatisticsPO po){
        log.info("销售首款情况：{}", JSON.toJSONString(po));
        return outService.sequenceToDetail(po);
    }

    /**
     * 更新销售出库单对冲状态
     */
    @SysLog(value = "更新销售出库单对冲状态", recordEntityChange = true, entityClass = SalesOut.class)
    @PostMapping("/updateHedgingState")
    @ApiOperation(value = "更新销售出库单对冲状态", notes = "用于变更销售订单的对冲状态和备注，状态可以0变成1也可以1变成0")
    public R updateHedgingState(@RequestBody UpdateHedgingStatePO po){
        log.info("更新销售出库单对冲状态：{}", JSON.toJSONString(po));
        try {
            return outService.updateHedgingState(po);
        } catch (Exception e) {
            e.printStackTrace();
            log.error("更新对冲状态错误信息:{}", e.getLocalizedMessage());
            return R.fail(e.getMessage());
        }
    }
}
